using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.OleDb;
using DTO;
using System.Data;

namespace DAO
{
    public class HoaDonMuaDao:IDaoObject<HoadonmuaDto,int>
    {
        #region IDaoObject<HoadonmuaDto,int> Members       
        //Tinh ThanhTien can phai nhap gia vao, o day tam khai bao string GiaNhap
        public HoadonmuaDto SaveOrUpdate(HoadonmuaDto obj)
        {            
            OleDbConnection connection = MyConnection.ConnectionData();            
            OleDbTransaction transaction = connection.BeginTransaction();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = connection;
            try
            {
                //transaction.Begin();
                cmd.Transaction = transaction;
                string sqlPattern;
                string sqlString;
                //Insert INTO hoadon table
                if (obj.Id > 0)
                {
                    #region Update hoadon table
                    sqlPattern = "Update Hoadon set Mahoadon='{0}', set Manhanvien = '{1}', set Ngaytao = '{2}', set Tongtien = '{3}', set Vat = '{4}', set Sotienthanhtoan = '{5}', set Loaihoadon={6}  ";
                    sqlString = String.Format(sqlPattern, obj.MaHoaDon, obj.MaNhanVien, obj.NgayTao, obj.TongTien, obj.Vat, obj.Sotienthanhtoan,obj.LoaiHd);
                    cmd = new OleDbCommand(sqlString, connection);
                    int hoadonId = cmd.ExecuteNonQuery();
                    obj.Id = hoadonId;
                    //Insert or update duocpham table
                    foreach (DictionaryEntry entry in obj.Duocpham)
                    {
                        DuocPhamDao dpdao = new DuocPhamDao();
                        DuocPhamDto dto = dpdao.SaveOrUpdate((DuocPhamDto)entry.Value);
                        //updat cthoadonmua table
                        sqlPattern = "Update CtHoadonmua set Soluongmua='" + dto.SoLuongMua + "', Thanhtien='" +
                            dto.GiaNhap * dto.SoLuongMua + "') where HoadonID=" + hoadonId + " and DuocphamID=" + dto.Id;
                        cmd.CommandText = sqlPattern;
                        cmd.ExecuteNonQuery();
                    }
                    #endregion
                }
                else
                {
                    #region Insert INTO Hoadon table, CTHoadon table and after that update the total of hoadon

                    obj.LoaiHd = (int)MyConnection.Loaihoadon.Hoadonmua;
                    sqlPattern = "insert into Hoadon(Mahoadon, Manhanvien, Ngaytao, Tongtien, Vat, Sotienthanhtoan, Loaihoadon)" +
                                 " values ('{0}','{1}','{2}','{3}',{4},{5},'{6}')";
                    string sqlString1 = String.Format(sqlPattern, obj.MaHoaDon, obj.MaNhanVien, obj.NgayTao,
                                                      obj.TongTien, obj.Vat, obj.Sotienthanhtoan, obj.LoaiHd);
                    cmd.CommandText = sqlString1;
                    cmd.ExecuteNonQuery();
                    #region get new Id

                    int hoadonId = MyConnection.GetnewId(transaction);
                    obj.Id = hoadonId;
                    #endregion
                    //float tongtien = 0;
                    //Insert or update duocpham table
                    foreach (DictionaryEntry entry1 in obj.Duocpham)
                    {
                        #region Save or Update Duocpham table
                        DuocPhamDao dpdao1 = new DuocPhamDao();
                        DuocPhamDto dto = dpdao1.SaveOrUpdate((DuocPhamDto)entry1.Value,transaction);
                        #endregion
                        #region Insert into Solo table
                        sqlPattern = "insert into Solo(HoadonmuaID, DuocphamID, Solo, Soluongnhap, Hansudung, Ngaysanxuat)" +
                                     " values ({0},{1},'{2}',{3},'{4}','{5}')";
                        sqlString = String.Format(sqlPattern, hoadonId, dto.Id, dto.SoLo, dto.SoLuongMua, dto.Hansudung, dto.Ngaysanxuat);
                        cmd.CommandText = sqlString;
                        cmd.ExecuteNonQuery();
                        #endregion
                        #region insert detail table
                        sqlPattern = "insert into CTHoadonmua(HoadonID, DuocphamID, Soluongmua, Thanhtien)" +
                                     " values ('{0}','{1}','{2}','{3}')";
                        sqlString = String.Format(sqlPattern, hoadonId, dto.Id, dto.SoLuongMua, dto.ThanhTien);
                        cmd.CommandText = sqlString;
                        cmd.ExecuteNonQuery();
                        #endregion
                    }
                    #endregion
                }
                transaction.Commit();
                return obj;
            }
                catch (Exception exception)
            {
                transaction.Rollback();
                throw new Exception(exception.Message);
            }
            return null;
        }

        public HoadonmuaDto SaveOrUpdate(HoadonmuaDto obj, OleDbTransaction transaction)
        {
            throw new NotImplementedException();
        }

        public void Delete(int id)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql = "Delete From CtHoadonmua Where CtHoadonmua.HoadonID='"+id
                +"' Delete from Hoadon where ID = '" + id + "' and Loaihoadon=0";
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public HoadonmuaDto Select(int id)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            #region select hoadon table
            string strSql = "select * from Hoadon where Loaihoadon = 1 and ID ='" + id + "'";
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            HoadonmuaDto hdm = new HoadonmuaDto();
            while (dr.Read())
            {
                hdm.Id = Convert.ToInt32(dr["ID"].ToString());
                hdm.MaHoaDon = dr["Mahoadon"].ToString();
                hdm.MaNhanVien = Convert.ToInt32(dr["Manhanvien"].ToString());
                hdm.NgayTao = Convert.ToDateTime(dr["Ngaytao"].ToString());
                hdm.TongTien = Convert.ToSingle(dr["Tongtien"].ToString());
                hdm.Vat = Convert.ToInt32(dr["Vat"].ToString());
                hdm.Sotienthanhtoan = Convert.ToSingle(dr["Sotienthanhtoan"].ToString());
            }
            dr.Close();
            cn.Close();
            #endregion
            #region select list duoc pham from CThoadon
            hdm.Duocpham = GetDuocpham(id);
            #endregion
            return hdm;

        }

        public HoadonmuaDto[] SelectAll()
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            const string strSql = " Select * from Hoadon where Loaihoadon = 1";
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, cn);
            DataTable datatable = new DataTable();
            dataAdapter.Fill(datatable);
            List<HoadonmuaDto> hdmdto = new List<HoadonmuaDto>();
            foreach (DataRow dr in datatable.Rows)
            {
                HoadonmuaDto hdm = new HoadonmuaDto();
                hdm.Id = Convert.ToInt32(dr["ID"].ToString());
                hdm.MaHoaDon = dr["Mahoadon"].ToString();
                hdm.MaNhanVien = Convert.ToInt32(dr["Manhanvien"].ToString());
                hdm.NgayTao = Convert.ToDateTime(dr["Ngaytao"].ToString());
                hdm.TongTien = Convert.ToSingle(dr["Tongtien"].ToString());
                hdm.Vat = Convert.ToInt32(dr["Vat"].ToString());
                hdm.Sotienthanhtoan = Convert.ToSingle(dr["Sotienthanhtoan"].ToString());
                hdm.Duocpham = GetDuocpham(hdm.Id);
                hdmdto.Add(hdm);
            }
            cn.Close();
            return hdmdto.ToArray();
        }

        #endregion

        private static Hashtable GetDuocpham(int id)
        {
            #region select list duoc pham from CThoadon

            try
            {
                const string strPattern = "select DuocphamID from CTHoadonmua where HoadonID={0}";
                string strSql = string.Format(strPattern, id);
                OleDbConnection cn = MyConnection.ConnectionData();
                OleDbDataAdapter dta = new OleDbDataAdapter(strSql, cn);
                DataTable dataTable = new DataTable();
                dta.Fill(dataTable);
                Hashtable hashDuocpham = new Hashtable();
                foreach (DataRow row in dataTable.Rows)
                {
                    DuocPhamDao dao = new DuocPhamDao();
                    DuocPhamDto dto = dao.Select(Convert.ToInt32(row["DuocphamID"].ToString()));
                    hashDuocpham.Add(dto.Id, dto);
                }
                return hashDuocpham;
            }
            catch (Exception exception)
            {
                
                throw new Exception(exception.Message);
            }
            return null;
            #endregion
        }
        public DataTable SearchDuocPhamByNameKey(string DuocphamID,string DuocphamName)
        {
            OleDbConnection con = MyConnection.ConnectionData();
            DataTable dt = new DataTable();
            string strquery = "select * from Duocpham where Tenduocpham=" + DuocphamName + "and Maduocpham="+ DuocphamID;
            OleDbDataAdapter adapter = new OleDbDataAdapter(strquery, con);
            adapter.Fill(dt);
            return dt;
        }
    }
}

       

       